﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using SocialIn.Core.Data;
using System.Data;
using System.Web;
using System.Web.Security;

namespace SocialIn.Biz
{
    public sealed class VoluntaryActivity
    {
        ConnectionStringSettings _BaseDBCon = null;

        public VoluntaryActivity()
        {
            _BaseDBCon = ConfigurationManager.ConnectionStrings[ConfigName.DBConfig];
        }

        public Result<DataTable> SearchVoluntaryActivity(int pageSize, int currentIndex
            , string location, string talent, string title)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@"declare @index int
declare @pageSize int

set @pageSize = {0}
set @index = {1}

select tempItems.*
from 
(	
select row_number() over(order by a.WishStartDate desc) as rownum
, a.Seq, a.NgoID,a.Title, a.TalentID, a.WishStartDate, a.WishEndDate
, b.NgoName, c.CodeName as TalentName, a.Place, c.Etc1 as ImagePath, c.Etc2 as [Desc]
from dbo.HelpRequest a
	inner join dbo.NGO b
		on a.ngoid = b.ngoid
	inner join dbo.CodeMaster c
			on c.Category = 'M003'
				and c.subcategory = a.talentid
where a.Canceled = 0
{2}
{3}
{4}
) tempItems
where tempItems.rownum 
		between ((@index - 1) * (@pageSize) + 1) 
		and (@index * (@pageSize))", pageSize, currentIndex
                , String.IsNullOrEmpty(location) ? "" : " and a.Place like '%" + location + "%' "
                , String.IsNullOrEmpty(talent) ? "" : " and a.TalentID = '" + talent + "'"
                , String.IsNullOrEmpty(title) ? "" : " and a.Title like '%" + title + "%'");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }

        public int GetVoluntaryActivityTotalCount(int pageSize, string location, string talent, string title)
        {
            string query = String.Format(@"select count(*)
from dbo.HelpRequest a
inner join dbo.NGO b
	on a.ngoid = b.ngoid
inner join dbo.CodeMaster c
		on c.Category = 'M003'
			and c.subcategory = a.talentid
where a.Canceled = 0
		{0}
		{1}
        {2}", String.IsNullOrEmpty(location) ? "" : " and a.Place like '%" + location + "%' "
                , String.IsNullOrEmpty(talent) ? "" : " and a.TalentID = '" + talent + "'"
                , String.IsNullOrEmpty(title) ? "" : " and a.Title like '%" + title + "%'");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<object> countResult = sql.ExecuteScalar(query);

            int itemCount = Convert.ToInt32(countResult.ReturnValue);

            int totalPagingCount = itemCount / pageSize;

            if ((itemCount % pageSize) != 0)
                totalPagingCount++;

            return totalPagingCount;
        }


        /// <summary>
        /// 봉사 상세건
        /// </summary>
        /// <returns></returns>
        public Result<DataTable> GetVoluntaryActivityView(string seq)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@"SELECT 
	                                            A.Seq, A.NgoID, A.Title, A.TalentID, A.Detail, A.WishStartDate, A.WishEndDate, A.ReceiptStartDate, A.ReceiptEndDate
	                                            , A.Place, A.MapX, A.MapY, A.MapPanoramaID, A.MapPan, A.MapTilt, A.MapZoom, A.Complement
	                                            , A.PersonInCharge, A.Contact, A.Writer, A.WriteDate
	                                            , B.NgoName, B.PersonInChargeID, B.PhoneNum
                                            FROM 
                                            (
	                                            SELECT 
		                                            Seq, NgoID, Title, TalentID, Detail, WishStartDate, WishEndDate, ReceiptStartDate, ReceiptEndDate
		                                            , Place, MapX, MapY, MapPanoramaID, MapPan, MapTilt, MapZoom, Complement
		                                            , PersonInCharge, Contact, Writer, WriteDate
	                                            FROM HelpRequest 
	                                            WHERE Seq = {0} 
	                                            AND Canceled = 0 
                                            ) AS A 
                                            INNER JOIN 
                                            (
	                                            SELECT 
		                                            A.NgoID, A.NgoName, A.PersonInChargeID, B.PhoneNum
	                                            FROM 
		                                            NGO AS A 
	                                            LEFT JOIN 
		                                            Member AS B 
	                                            ON A.PersonInChargeID = B.UserID 
                                            ) AS B 
                                            ON A.NgoID = B.NgoID ", seq.Trim());
            // seq

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }



        /// <summary>
        /// 본인이 등록한 글인지를 확인한다.
        /// </summary>
        /// <param name="seq"></param>
        /// <returns></returns>
        public Result<DataTable> GetIVolunteerWrite(string seq)
        {
            Member member = (Member)HttpContext.Current.Session[ConfigName.SessionMember];
            Result<DataTable> resultValue = null;

            string query = String.Format(@"SELECT 
                                                A.Seq, A.NgoID, A.Title, A.TalentID, A.Detail, A.WishStartDate, A.WishEndDate, A.ReceiptStartDate, A.ReceiptEndDate
                                                , A.Place, A.MapX, A.MapY, A.MapPanoramaID, A.MapPan, A.MapTilt, A.MapZoom, A.Complement, A.Completed
                                                , A.PersonInCharge, A.Contact, A.Writer, A.WriteDate
                                                , B.NgoName, B.PersonInChargeID, B.PhoneNum
                                            FROM 
                                            (
                                                SELECT 
                                                    Seq, NgoID, Title, TalentID, Detail, WishStartDate, WishEndDate, ReceiptStartDate, ReceiptEndDate
                                                    , Place, MapX, MapY, MapPanoramaID, MapPan, MapTilt, MapZoom, Complement
                                                    , PersonInCharge, Contact, Writer, WriteDate, Completed
                                                FROM HelpRequest 
                                                WHERE Seq = {0} 
                                                AND Canceled = 0 
                                            ) AS A 
                                            INNER JOIN 
                                            (
                                                SELECT 
                                                    A.NgoID, A.NgoName, A.PersonInChargeID, B.PhoneNum
                                                FROM 
                                                    NGO AS A 
                                                LEFT JOIN 
                                                    Member AS B 
                                                ON A.PersonInChargeID = B.UserID 
	                                            WHERE B.UserID = '{1}'
                                            ) AS B 
                                            ON A.NgoID = B.NgoID  ", seq.Trim(), member.UserID);
           

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }


        /// <summary>
        /// 해당 건 봉사자 목록을 조회한다.
        /// </summary>
        /// <param name="seq"></param>
        /// <returns></returns>
        public Result<DataTable> GetVolunteerList(string seq)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@" SELECT  A.USERID, A.STATUS, A.WRITEDATE  , B.UserName, C.Complement
                                            FROM 
	                                            Volunteer AS A 
                                            INNER JOIN 
	                                            Member AS B 
                                            ON A.USERID = B.USERID 
                                            LEFT JOIN 
	                                            HelpRequest AS C
                                            ON A.HelpRequestID = C.Seq
                                            WHERE A.HelpRequestID = {0} ", seq.Trim());

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }


        /// <summary>
        /// 해당 건 봉사자 목록을 조회한다. - 받으미 기준에서 지원자를 선택합니다.
        /// </summary>
        /// <param name="seq"></param>
        /// <returns></returns>
        public Result<DataTable> GetVolunteerByBadmiList(string seq)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@" SELECT  A.USERID, A.STATUS, A.WRITEDATE  , B.UserName
                                        FROM 
	                                        Volunteer AS A 
                                        INNER JOIN 
	                                        Member AS B 
                                        ON A.USERID = B.USERID
                                        WHERE A.HelpRequestID = {0} AND STATUS NOT IN ( 'V003' ) ", seq.Trim());

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }


        /// <summary>
        /// 지원을 했는지를 확인한다.
        /// </summary>
        /// <param name="seq"></param>
        /// <returns></returns>
        public Result<DataTable> GetIsVolunteer(string seq)
        {
            Member member = (Member)HttpContext.Current.Session[ConfigName.SessionMember];

            string query = String.Format(@" SELECT 
	                                            A.USERID, A.STATUS, A.WRITEDATE , B.UserName
                                            FROM 
	                                            Volunteer AS A 
                                            INNER JOIN 
	                                            Member AS B 
                                            ON A.USERID = B.USERID
                                            WHERE A.HelpRequestID = {0} AND A.USERID = '{1}'  ", seq.Trim(), member.UserID.Trim());

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);

            Result<DataTable> result = sql.ExecuteDataTable(query);

            return result;
        }


        /// <summary>
        /// 감사 후기 내용
        /// </summary>
        /// <param name="seq"></param>
        /// <returns></returns>
        public Result<DataTable> GetVoluntaryAfterNote(string seq)
        {
            Result<DataTable> resultValue = null;

            string query = String.Format(@" SELECT * FROM AfterNote WHERE HelpRequestID = {0} ", seq.Trim());
            // seq

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            resultValue = sql.ExecuteDataTable(query, null);

            return resultValue;
        }

        /// <summary>
        /// 봉사건 삭제
        /// </summary>
        /// <param name="helpRequestID"></param>
        /// <returns></returns>
        public int SetHelpRequestDelete(string helpRequestID)
        {
            string query = string.Format(@"   UPDATE HelpRequest 
                                SET Canceled = 1 
                                WHERE Seq = @Seq ", helpRequestID);

            Member member = (Member)HttpContext.Current.Session[ConfigName.SessionMember];


            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<int> dtResult = sql.ExecuteNonQuery(query);

            return dtResult.ReturnValue;
        }

        /// <summary>
        /// 자원 봉사 지원 추가
        /// </summary>
        /// <param name="helpRequestID"></param>
        /// <returns></returns>
        public int SetVolunteerApply(string helpRequestID)
        {
            string query = @" INSERT INTO Volunteer (HelpRequestID ,UserID ,Status ,Writer ,WriteDate ,Editor ,EditDate) 
                            VALUES (@HelpRequestID ,@UserID ,@Status ,@Writer ,GETDATE() ,@Editor ,GETDATE())";

            Member member = (Member)HttpContext.Current.Session[ConfigName.SessionMember];

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@HelpRequestID", helpRequestID);
            parameters.Add("@UserID", member.UserID);
            parameters.Add("@Status", "V001");
            parameters.Add("@Writer", member.UserID);
            parameters.Add("@Editor", member.UserID);

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<int> dtResult = sql.ExecuteNonQuery(query, parameters);

            return dtResult.ReturnValue;
        }


        /// <summary>
        /// 받으미가 자원봉사자 상태값을 변경합니다.
        /// </summary>
        /// <param name="helpRequestID"></param>
        /// <returns></returns>
        public int SetVolunteerStatusModify(string helpRequestID, string userID, string status)
        {
            string query = @" UPDATE Volunteer
                                SET SATATUS = @SATATUS, Editor = @Editor, EditDate = GETDATE() 
                                WHERE HelpRequestID = @HelpRequestID AND USERID = @USERID ";

            Member member = (Member)HttpContext.Current.Session[ConfigName.SessionMember];

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@HelpRequestID", helpRequestID);
            parameters.Add("@USERID", userID);
            parameters.Add("@SATATUS", status);
            parameters.Add("@Editor", member.UserID);

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<int> dtResult = sql.ExecuteNonQuery(query, parameters);

            return dtResult.ReturnValue;
        }

        public bool RegisterHelpRequest(string userID, string title,string talentID , string detail, DateTime wishDateStart, DateTime wishDateEnd
            , string place, string mapX, string mapY, string mapPanoramaID, string mapPan, string mapTit, int mapZoom, int complement
            )
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);

            Result<DataTable> tempNgo = sql.ExecuteDataTable(String.Format("select * from ngo where PersonInChargeID = '{0}'", userID));            

            string query = String.Format(@"INSERT INTO
  dbo.HelpRequest(
  NgoID, Title, TalentID, Detail, WishStartDate, WishEndDate, ReceiptStartDate, ReceiptEndDate, Place, MapX, MapY, MapPanoramaID, MapPan,
  MapTilt, MapZoom, Complement, PersonInCharge, Contact, Writer, Editor)
VALUES(
  {0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', {14}, {15},
  '{16}', '{17}', '{18}', '{19}')"
, tempNgo.ReturnValue.Rows[0]["NgoID"], title, talentID, detail
, wishDateStart.ToString("yyyy-MM-dd hh:mm:ss"), wishDateEnd.ToString("yyyy-MM-dd hh:mm:ss")
, wishDateStart.ToString("yyyyMMdd"), wishDateEnd.ToString("yyyyMMdd")
, place, mapX, mapY, mapPanoramaID, mapPan, mapTit, mapZoom, complement, userID, tempNgo.ReturnValue.Rows[0]["Contact"]
, userID, userID);
            
            Result<int> resultValue = sql.ExecuteNonQuery(query, null);

            return resultValue.Completed;
        }
    }
}
